--================================================
--  Create database insert trigger on CHIETKHAU
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_CK','TR') IS NOT NULL
   DROP TRIGGER TR_INS_CK
GO

CREATE TRIGGER TR_INS_CK
	ON dbo.CHIETKHAU
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @MaMH char(10)
	DECLARE @SL smallint
	DECLARE @TL float
	SELECT @MaMH = MaMatHang, @SL = SLToiThieu, @TL = TLChietKhau FROM INSERTED

	IF EXISTS(SELECT * FROM dbo.CHIETKHAU WHERE MaMatHang = @MaMH AND SLToiThieu = @SL)
	BEGIN
		RAISERROR('Mức chiết khấu này đã tồn tại', 16, 01);
		ROLLBACK TRAN
		RETURN
	END

	IF(@TL>1)
	BEGIN
		RAISERROR('Tỷ lệ chiết khấu phải nhỏ hơn 100%%', 16, 02);
		ROLLBACK TRAN
		RETURN
	END

	INSERT INTO dbo.CHIETKHAU(MaChietKhau, MaMatHang, SLToiThieu, TLChietKhau)
		VALUES('', @MaMH, @SL, @TL)
	DECLARE @id char(10) 
	EXEC dbo.NEW_CHIETKHAU @id OUTPUT
GO

--================================================
--  Create database insert trigger on CTHOADON
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_CTHD','TR') IS NOT NULL
   DROP TRIGGER TR_INS_CTHD
GO

CREATE TRIGGER TR_INS_CTHD
	ON dbo.CTHOADON
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @MaHD char(10)
	DECLARE @MaMH char(10)
	DECLARE @SL smallint
	SELECT @MaHD = MaHoaDon, @MaMH = MaMatHang, @SL = SoLuong FROM INSERTED

	IF EXISTS(SELECT * FROM dbo.CTHOADON WHERE MaHoaDon = @MaHD AND MaMatHang = @MaMH)
	BEGIN
		UPDATE dbo.CTHOADON 
			SET SoLuong+=@SL
			WHERE MaHoaDon = @MaHD AND MaMatHang = @MaMH
	END
	ELSE
	BEGIN
		INSERT INTO dbo.CTHOADON(MaChiTiet, MaHoaDon, MaMatHang,SoLuong)
			VALUES('', @MaHD, @MaMH, @SL)

		DECLARE @id char(10)
		EXEC dbo.NEW_CTHOADON @id OUTPUT
		EXEC dbo.DEF_CTHOADON @id
	END
GO

--================================================
--  Create database insert trigger on CTPHIEUNHAP
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_CTPN','TR') IS NOT NULL
   DROP TRIGGER TR_INS_CTPN
GO

CREATE TRIGGER TR_INS_CTPN
	ON dbo.CTPHIEUNHAP
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @MaPN char(10)
	DECLARE @MaMH char(10)
	DECLARE @SL smallint
	DECLARE @HSD date
	DECLARE @DonGia int
	
	SELECT @MaPN = MaPhieu, @MaMH = MaMatHang, @SL = SoLuongNhap, @HSD = HanSuDung, @DonGia = DonGiaNhap
		FROM INSERTED

	DECLARE @coHSD bit
	DECLARE @id char(10)
	SELECT @coHSD = HanSuDung FROM dbo.MATHANG WHERE MaMatHang = @MaMH

	IF(@coHSD = 1)
	BEGIN
		SELECT @id = MaChiTiet FROM dbo.CTPHIEUNHAP WHERE MaPhieu = @MaPN AND MaMatHang = @MaMH AND HanSuDung = @HSD
		IF @id IS NOT NULL
		BEGIN
			UPDATE dbo.CTPHIEUNHAP 
				SET SoLuongNhap+=@SL
				WHERE MaChiTiet = @id
		END
		ELSE
		BEGIN
			INSERT INTO dbo.CTPHIEUNHAP(MaChiTiet, MaPhieu, MaMatHang, HanSuDung, SoLuongNhap, DonGiaNhap)
				VALUES('', @MaPN, @MaMH, @HSD, @SL, @DonGia)
			EXEC dbo.NEW_CTPHIEUNHAP @id OUTPUT
		END
	END
	ELSE
	BEGIN
		SELECT @id = MaChiTiet FROM dbo.CTPHIEUNHAP WHERE MaPhieu = @MaPN AND MaMatHang = @MaMH
		IF @id IS NOT NULL
		BEGIN
			UPDATE dbo.CTPHIEUNHAP 
				SET SoLuongNhap+=@SL
				WHERE MaChiTiet = @id
		END
		ELSE
		BEGIN
			INSERT INTO dbo.CTPHIEUNHAP(MaChiTiet, MaPhieu, MaMatHang, HanSuDung, SoLuongNhap, DonGiaNhap)
				VALUES('', @MaPN, @MaMH, NULL, @SL, @DonGia)
			EXEC dbo.NEW_CTPHIEUNHAP @id OUTPUT
		END
	END	
	EXEC dbo.DEF_CTPHIEUNHAP @id
GO

--================================================
--  Create database insert trigger on GIANIEMYET
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_GNY','TR') IS NOT NULL
   DROP TRIGGER TR_INS_GNY
GO

CREATE TRIGGER TR_INS_GNY
	ON dbo.GIANIEMYET
	AFTER INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @id char(10)
	EXEC dbo.NEW_GIANIEMYET @id OUTPUT
GO

--================================================
--  Create database insert trigger on HOADON
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_HD','TR') IS NOT NULL
   DROP TRIGGER TR_INS_HD
GO

CREATE TRIGGER TR_INS_HD
	ON dbo.HOADON
	AFTER INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @id char(10)
	EXEC dbo.NEW_HOADON @id OUTPUT
GO

--================================================
--  Create database insert trigger on LOAIMATHANG
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_LMH','TR') IS NOT NULL
   DROP TRIGGER TR_INS_LMH
GO

CREATE TRIGGER TR_INS_LMH
	ON dbo.LOAIMATHANG
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @Ten nvarchar(30)
	SELECT @Ten = TenLoai FROM INSERTED

	DECLARE @id char(10)
	SELECT @id = MaLoai FROM dbo.LOAIMATHANG WHERE TenLoai = @Ten
	IF @id IS NOT NULL
	BEGIN
		IF((SELECT TrangThai FROM dbo.LOAIMATHANG WHERE MaLoai = @id) = 1)
		BEGIN
			RAISERROR('Loại mặt hàng đang tồn tại trong hệ thống', 16, 03);
			ROLLBACK TRAN
			RETURN
		END
	END
	ELSE
	BEGIN
		EXEC dbo.NEW_GIANIEMYET @id OUTPUT
	END
	EXEC dbo.DEF_LOAIMATHANG @id
GO

--================================================
--  Create database insert trigger on LOAINHANVIEN
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_LNV','TR') IS NOT NULL
   DROP TRIGGER TR_INS_LNV
GO

CREATE TRIGGER TR_INS_LNV
	ON dbo.LOAINHANVIEN
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @Ten nvarchar(30)
	SELECT @Ten = TenLoai FROM INSERTED

	IF EXISTS(SELECT * FROM dbo.LOAINHANVIEN WHERE TenLoai = @Ten)
	BEGIN
		RAISERROR('Loại nhân viên này đang tồn tại trong hệ thống', 16, 04);
		ROLLBACK TRAN
		RETURN
	END
	
	DECLARE @id char(10)
	EXEC dbo.NEW_LOAINHANVIEN @id OUTPUT
GO

--================================================
--  Create database insert trigger on LUONGNV
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_LUO','TR') IS NOT NULL
   DROP TRIGGER TR_INS_LUO
GO

CREATE TRIGGER TR_INS_LUO
	ON dbo.LUONGNV
	INSTEAD OF INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @MaNV char(10)
	DECLARE @Thang tinyint
	DECLARE @Nam smallint
	SELECT @MaNV = MaNhanVien, @Thang = Thang, @Nam = Nam FROM INSERTED
	IF EXISTS(SELECT * FROM dbo.LUONGNV WHERE MaNhanVien = @MaNV AND Thang = @Thang AND Nam = @Nam)
	BEGIN
		RAISERROR('Lương của nhân viên %s trong tháng %d năm %d đã tồn tại', 16, 05, @MaNV, @Thang, @Nam);
		ROLLBACK TRAN
		RETURN
	END

	DECLARE @id char(10)
	EXEC dbo.NEW_LUONGNV @id OUTPUT
	EXEC dbo.DEF_LUONGNV @id
GO

--================================================
--  Create database insert trigger on MATHANG
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_MH','TR') IS NOT NULL
   DROP TRIGGER TR_INS_MH
GO

CREATE TRIGGER TR_INS_MH
	ON dbo.MATHANG
	AFTER INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @id char(10)
	EXEC dbo.NEW_MATHANG @id OUTPUT
	EXEC dbo.DEF_MATHANG @id
	INSERT INTO dbo.GIANIEMYET(MaGia, MaMatHang) VALUES('', @id)
GO

--================================================
--  Create database insert trigger on NHANVIEN
--================================================
USE DAILY
GO

IF OBJECT_ID ('TR_INS_NV','TR') IS NOT NULL
   DROP TRIGGER TR_INS_NV
GO

CREATE TRIGGER TR_INS_NV
	ON dbo.NHANVIEN
	AFTER INSERT
AS 
	SET NOCOUNT ON;
	DECLARE @id char(10)
	EXEC dbo.NEW_NHANVIEN @id OUTPUT
	EXEC dbo.DEF_NHANVIEN @id
	INSERT INTO dbo.TKDANGNHAP(MaTaiKhoan, TenDangNhap, MaNhanVien)	VALUES('', 'Empl' + RIGHT(@id, 8), @id)
GO

